package cn.edu.klmy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import web.util.LobOperator;
import cn.edu.klmy.dao.ITeacherDao;
import cn.edu.klmy.models.teacher.Teacher;

import com.googlecode.ehcache.annotations.Cacheable;
import com.googlecode.ehcache.annotations.KeyGenerator;
import com.googlecode.ehcache.annotations.PartialCacheKey;
import com.googlecode.ehcache.annotations.Property;
import com.googlecode.ehcache.annotations.TriggersRemove;
import com.googlecode.ehcache.annotations.When;

@Repository
public class TeacherDao extends JdbcDaoSupport implements ITeacherDao {
    private static final String ADD_TEACHER = "INSERT INTO TEACHER(TEACHER_NAME, TEACHER_ID_NO, TEACHER_SEX, TEACHER_RACE, TEACHER_BIRTH, TEACHER_GRADE, TEACHER_SUBJECT) VALUES(?, ?, ?, ?, ?, ?, ?)";

    @Override
    public void addTeacher(Teacher teacher) {
        getJdbcTemplate().update(ADD_TEACHER, new Object[]{
                teacher.getName(),
                teacher.getIdentityCardNumber(),
                teacher.getSex(),
                teacher.getNation(),
                teacher.getBirthday(),
                teacher.getGrade(),
                teacher.getSubject()
        });
    }

    private static final String REMOVE_TEACHER = "DELETE FROM TEACHER WHERE ID = ?";

    @Override
    @TriggersRemove(cacheName = "teacherPictureCache", when = When.AFTER_METHOD_INVOCATION,
            keyGenerator = @KeyGenerator(
                    name = "ListCacheKeyGenerator",
                    properties = @Property(name = "includeMethod", value = "false")
            )
    )
    public void removeTeacher(int teacherId) {
        getJdbcTemplate().update(REMOVE_TEACHER, new Object[]{teacherId});
    }


    private static final String UPDATE_TEACHER = "UPDATE teacher SET TEACHER_NAME = ?, TEACHER_ID_NO = ?, TEACHER_SEX = ?, TEACHER_RACE = ?, TEACHER_BIRTH = ?, TEACHER_GRADE = ?, TEACHER_SUBJECT = ? WHERE id = ?";

    @Override
    public void updateTeacher(Teacher teacher) {
        getJdbcTemplate().update(UPDATE_TEACHER, new Object[]{
                teacher.getName(),
                teacher.getIdentityCardNumber(),
                teacher.getSex(),
                teacher.getNation(),
                teacher.getBirthday(),
                teacher.getGrade(),
                teacher.getSubject(),
                teacher.getId()
        });
    }

    public static final String GET_TEACHER = "SELECT * FROM teacher WHERE ";

    @Override
    public Teacher getTeacher(String fieldName, Object value) {
        if (fieldName == null)
            return null;
        String sql = GET_TEACHER + fieldName + "=" + value;
        List<Teacher> teachers = (List<Teacher>) getJdbcTemplate().query(sql, new Object[]{}, new TeacherRowMapper());
        if (null == teachers || teachers.isEmpty())
            return null;
        return teachers.get(0);
    }

    private static final String TEACHER_EXISTS = "SELECT COUNT(*) FROM teacher WHERE id = ?";

    @Override
    public boolean teacherExists(int teacherId) {
        int result = getJdbcTemplate().queryForInt(TEACHER_EXISTS, new Object[]{teacherId});

        if (result < 1) {
            return false;
        }
        return true;
    }

    private static final String TEACHER_EXISTS_BY = "SELECT COUNT(*) FROM teacher WHERE TEACHER_ID_NO = ?";

    @Override
    public boolean teacherExistsByIdentityCardNumber(String identityCardNumber) {
        int result = getJdbcTemplate().queryForInt(TEACHER_EXISTS_BY, new Object[]{identityCardNumber});

        if (result < 1) {
            return false;
        }
        return true;
    }

    //顾伟添加
    public List<Teacher> getAllTeachers(){
        String sql="SELECT * FROM TEACHER";
        return getJdbcTemplate().query(sql,new TeacherRowMapper());
    }

   @Override
    public List<Teacher> getTeachers(Map<String, Object[]> filter, int pageNum, int pageSize) {
        String sql = DaoUtil.getFuzzyQueryPageSql("TEACHER", filter, pageNum, pageSize);
        return (List<Teacher>) getJdbcTemplate().query(sql, new Object[]{}, new TeacherRowMapper());
    }

    @Override
    public int getTeacherCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("TEACHER", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    private static final String GET_PICTURE = "SELECT TEACHER_ICO FROM TEACHER WHERE ID = ?";

    @Override
    @Cacheable(cacheName = "teacherPictureCache",
            keyGenerator = @KeyGenerator(
                    name = "ListCacheKeyGenerator",
                    properties = @Property(name = "includeMethod", value = "false")
            )
    )
    public byte[] getTeacherPicture(int teacherId) {
        return LobOperator.getLob(getJdbcTemplate(), GET_PICTURE, new Object[]{teacherId});
    }

    private static final String SAVE_PICTURE = "UPDATE TEACHER SET TEACHER_ICO = ? WHERE ID = ?";

    @Override
    @TriggersRemove(cacheName = "teacherPictureCache", when = When.AFTER_METHOD_INVOCATION,
            keyGenerator = @KeyGenerator(
                    name = "ListCacheKeyGenerator",
                    properties = @Property(name = "includeMethod", value = "false")
            )
    )
    public void setTeacherPicture(@PartialCacheKey int teacherId, byte[] picture) {
        LobOperator.saveLob(getJdbcTemplate(), SAVE_PICTURE, teacherId, picture);
    }

    private static final String GET_TEACHERS_IN_CLASZ = "SELECT ID, TEACHER_NAME, TEACHER_ID_NO, TEACHER_SEX, TEACHER_RACE, TEACHER_BIRTH, TEACHER_GRADE, TEACHER_SUBJECT FROM TEACHER WHERE ID IN (SELECT TEACHER_ID FROM TEACHER_CLASSES WHERE CLASS_ID = ?)";

    @Override
    public List<Teacher> getTeachersInClasz(int claszId) {
        return (List<Teacher>) getJdbcTemplate().query(GET_TEACHERS_IN_CLASZ, new Object[]{ claszId }, new TeacherRowMapper());
    }

    private static final String GET_TEACHERS_NOT_IN_CLASZ = "SELECT ID, TEACHER_NAME, TEACHER_ID_NO, TEACHER_SEX, TEACHER_RACE, TEACHER_BIRTH, TEACHER_GRADE, TEACHER_SUBJECT FROM TEACHER WHERE ID NOT IN (SELECT TEACHER_ID FROM TEACHER_CLASSES WHERE CLASS_ID = ?)";
    @Override
    public List<Teacher> getTeachersNotInClasz(int claszId) {
        return (List<Teacher>) getJdbcTemplate().query(GET_TEACHERS_NOT_IN_CLASZ, new Object[]{ claszId }, new TeacherRowMapper());
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    private static class TeacherRowMapper implements RowMapper<Teacher> {
        @Override
        public Teacher mapRow(ResultSet resultSet, int i) throws SQLException {
            Teacher teacher = new Teacher();
            teacher.setId(resultSet.getInt("ID"));
            teacher.setBirthday(resultSet.getString("TEACHER_BIRTH"));
            teacher.setName(resultSet.getString("TEACHER_NAME"));
            teacher.setNation(resultSet.getInt("TEACHER_RACE"));
            teacher.setSex(resultSet.getInt("TEACHER_SEX"));
            teacher.setIdentityCardNumber(resultSet.getString("TEACHER_ID_NO"));
            teacher.setSubject(resultSet.getString("TEACHER_SUBJECT"));
            teacher.setGrade(resultSet.getString("TEACHER_GRADE"));
            return teacher;   //TEACHER_NAME, TEACHER_ID_NO, TEACHER_SEX, TEACHER_RACE, TEACHER_BIRTH, TEACHER_GRADE, TEACHER_SUBJECT
        }
    }
}
